<?php
namespace app\admin\controller;
use think\Validate;
use think\Loader;
use think\Controller;
use think\Db;
use think\Session;
use think\Request;//首先引入 Request 文件；
//use app\Gas\controller\Base;
//use app\Gas\model\Gas as GasModel; //改名，否则会有两个Gas类，报错
session_start();
header('Content-Type:application/json; charset=utf-8');
header("Access-Control-Allow-Origin: *");//允许跨域请求
class Gas extends Controller{ //天然气公司
	/*public function _initialize(){ //在类里执行任何方法之前都要执行这个方法
      if(!session('username')){
      	$this->error('非法进入，请先登录！','Login/index');
      }
	}*/

	public function index(){ //首页
		$comid=$_POST['comid'];
		$num_ren=Db::query("select count(cid) from tp_broker where comid='".$comid."' ");//经纪人总数
		$num_baodan=Db::query("select count(tp_policy.id) from tp_policy,tp_broker where tp_policy.cid=tp_broker.cid and tp_broker.comid='".$comid."' ");//本公司保单总数
		$num_sales=Db::query("select SUM(tp_policy.zongji) from tp_policy,tp_broker where tp_policy.cid=tp_broker.cid and tp_broker.comid='".$comid."' ");//销售额总数
		$info['num_ren']=$num_ren[0]['count(cid)'];
		$info['num_baodan']=$num_baodan[0]['count(tp_policy.id)'];
		$info['num_sales']=$num_sales[0]['SUM(tp_policy.zongji)'];
		return json_encode($info,JSON_UNESCAPED_UNICODE);
	}
	public function self_info(){ //燃气公司信息
		$comid=$_POST['comid'];
		$self_info=Db::name('natural_gas_company')->where('comid',$comid)->find();
		return json_encode($self_info,JSON_UNESCAPED_UNICODE);
	}
	public function ren_info(){ //燃气公司经纪人信息(可按名字查询)
		$comid=$_POST['comid'];
		if(isset($_POST['tiaojian'])){
			$ren_info=Db::query("select * from tp_broker where comid='".$comid."' and name LIKE '%".$_POST['tiaojian']."%' ");
			$info['status']='ok';
			$info['list']=$ren_info;
			return json_encode($info,JSON_UNESCAPED_UNICODE);
		}else{
			$ren_info=Db::name('broker')->where('comid',$comid)->select();
			$info['status']='ok';
			$info['list']=$ren_info;
			return json_encode($info,JSON_UNESCAPED_UNICODE);
		}
	}
	public function batch_import(){ //批量导入经纪人
		$dir=dirname(__FILE__);
		require $dir.'/PHPExcel/PHPExcel/IOFactory.php';
		$comid=$_POST['comid'];
		if(request()->isPost()){
			if(isset($_FILES['import']['tmp_name'])){
                $file=request()->file('import');
                $info = $file->validate(['size'=>10485760,'ext'=>'xlsx,xls'])->move( 'apply' . DS . 'file');//10M
                $filename="D:\phpstudy\WWW/bx/public/apply/file/".$info->getSaveName();
                //$filename=$dir."/test.xlsx";
				$objPHPExcel=\PHPExcel_IOFactory::load($filename);//记载文件

				$sheetCount=$objPHPExcel->getSheetCount();//获取excel文件里有多少个sheet
				for($i=0;$i<$sheetCount;$i++){
				    $data=$objPHPExcel->getSheet($i)->toArray();//读取每个sheet里的数据全部放到数组中
				}
				for($j=1;$j<count($data);$j++){
				 $insert['name']=$data[$j][0];
				 $insert['sex']=$data[$j][1];
				 $insert['type']=$data[$j][2];
				 $insert['idcard']=$data[$j][3];
				 $insert['work']=$data[$j][4];
				 $insert['tel']=$data[$j][5];
				 $insert['email']=$data[$j][6];
				 $insert['address']=$data[$j][7];
				 $insert['comid']=$comid;
				 $insert['wid']=$data[$j][8];
				 $insert['log_id']=$data[$j][9];
				 $insert['log_psd']=$data[$j][10];
				 $insert['cid']=GetRandStr(10);

				 //dump($insert).'<br/>';
				 $validate = Loader::validate('Admin');
	            if(!$validate->scene('add')->check($insert)){  //应用验证
	               $info=array('desc'=>$validate->getError());
	               return json_encode($info,JSON_UNESCAPED_UNICODE);
	            }

				 if(db('broker')->insert($insert)){
				 	$info='ok';
				    echo json_encode($info,JSON_UNESCAPED_UNICODE);
				 }else{
				 	$info='no';
				    echo json_encode($info,JSON_UNESCAPED_UNICODE);
				 }
           }

         }
	 }
	}
	public function log_info(){ //燃气公司经纪人登录日志(可按名字查询)
		if(isset($_POST['tiaojian'])){
			$log_info['list']=Db::query("select * from tp_logon_log where name LIKE '%".$_POST['tiaojian']."%' ");
	        return json_encode($log_info,JSON_UNESCAPED_UNICODE);
		}else{
			$log_info['list']=Db::query("select * from tp_logon_log ");
		    return json_encode($log_info,JSON_UNESCAPED_UNICODE);
		}
	}
	public function entry(){ //燃气公司销售记录
		$comid=$_POST['comid'];
	    $sales_record=Db::query("select tp_policy.* from tp_policy,tp_broker where tp_policy.cid=tp_broker.cid and tp_broker.comid='".$comid."' ");
		return json_encode($sales_record,JSON_UNESCAPED_UNICODE);
	}
	public function broker(){ //返回所有经纪人的姓名和cid
		$broker=Db::name('broker')->column("name","cid");
		return json_encode($broker,JSON_UNESCAPED_UNICODE);
	}
	public function add_policy(){ //录入新的保单
	    if(request()->isPost()){
			$data=[
			   'orderid'=>GetRandStr(10), //保单编码
			   'baodanhao'=>GetRandStr(10),
			   'cid'=>input('cid'),
			   'pid'=>'YIoNFKPMQr', //产品编码
			   'user'=>input('user'),
			   'user_idcard'=>input('user_idcard'),
			   'user_tel'=>input('user_tel'),
			   'ranqihao'=>input('ranqihao'),
			   'rqtype'=>input('rqtype'),
			   'rqposition'=>input('rqposition'),
			   'term'=>input('term'),//保险期限(1年、2年...5年)
			   'etime'=>time()+(input('term')*31536000),//保险止期
			   'qishu'=>input('qishu'),//保险份数
			   'baofei'=>input('baofei'),//每份保费(需要前台保存并渲染到表单改为不可更改)
			   'zongji'=>input('zongji'),//总保费
			   'rdatetime'=>time(),//录包时间
			];
			$interface=[    //保险接口调用记录
			   'oderid'=>$data['orderid'],
			   'comid'=>$comid,
			   'intername'=>'录保',
			   'dtime'=>time(),
		    ];
            $pay=[    //保单支付信息(假支付)
               'oderid'=>$data['orderid'],
               'name'=>$data['user'],
               'zongji'=>input('zongji'),
               'pay'=>input('zongji'),
               'paymethod'=>'微信',
               'payno'=>GetRandStr(15),
               'paytime'=>time(),
            ];

			$validate = Loader::validate('insure');
			if(!$validate->scene('add')->check($data)){  //应用验证
			   $insure_info=array('message'=>$validate->getError());
               return json_encode($insure_info,JSON_UNESCAPED_UNICODE);
			}

			if(db('policy')->insert($data)){ //助手函数添加(投保成功)
                $insure_info['message']='ok';
                $interface['status']='1';
                db('interface')->insert($interface);//插入接口调用表
                db('pay')->insert($pay);//插入保单支付表
                return json_encode($insure_info,JSON_UNESCAPED_UNICODE);
			}else{
				$interface['status']='2';
                db('interface')->insert($interface);//插入接口调用表
				$insure_info['message']='no';
                return json_encode($insure_info,JSON_UNESCAPED_UNICODE);
			}
		}
	}
	public function batch_insurance(){ //批量投保
		$comid=$_POST['comid'];
		$dir=dirname(__FILE__);
		require $dir.'/PHPExcel/PHPExcel/IOFactory.php';
		if(request()->isPost()){
			$arr=request()->param();
			if(isset($_FILES['import']['tmp_name'])){
                $file=request()->file('import');
                $info = $file->validate(['size'=>10485760,'ext'=>'xlsx,xls'])->move( 'apply' . DS . 'toubao');//10M
                $filename="D:\phpstudy\WWW/bx/public/apply/toubao/".$info->getSaveName();
                //$filename=$dir."/test.xlsx";
				$objPHPExcel=\PHPExcel_IOFactory::load($filename);//记载文件

				$sheetCount=$objPHPExcel->getSheetCount();//获取excel文件里有多少个sheet
				for($i=0;$i<$sheetCount;$i++){
				    $data=$objPHPExcel->getSheet($i)->toArray();//读取每个sheet里的数据全部放到数组中
				}
				$chongfu=0;
				for($j=1;$j<count($data);$j++){
				 $insert['orderid']=GetRandStr(10);
				 $insert['baodanhao']=GetRandStr(10);
				 $insert['pid']='YIoNFKPMQr';
				 $insert['user']=$data[$j][0];
				 $insert['user_idcard']=$data[$j][1];
				 $insert['user_tel']=$data[$j][2];
				 $insert['ranqihao']=$data[$j][3];
				 $insert['rqtype']=$data[$j][4];
				 $insert['rqposition']=$data[$j][5];
				 $insert['term']=$data[$j][6];
				 $insert['etime']=$data[$j][7];
				 $insert['qishu']=$data[$j][8];
				 $insert['baofei']=$data[$j][9];
				 $insert['zongji']=$data[$j][10];
				 $insert['cid']=$data[$j][11];
				 $insert['rdatetime']=time();

                 if(!isset($arr['check']) && !isset($arr['tijiao'])){ //把excel数据返回到页面
                 	dump($insert).'<br/>';
                 }
                  if(isset($arr['check'])){ //点击检索 返回空说明检索通过
	                  	if($j>=2 && $data[$j][1]==$data[$j-1][1]){
	                  		$message['desc']='身份证号有重复,禁止提交!';
				            return json_encode($message,JSON_UNESCAPED_UNICODE);
	                  	  }
						 $validate = Loader::validate('Insure');
			            if(!$validate->scene('add')->check($insert)){  //应用验证
			               $message=array('desc'=>$validate->getError());
			               return json_encode($message,JSON_UNESCAPED_UNICODE);
			            }
			       }
				 $interface=[    //保险接口调用记录
				   'oderid'=>$insert['orderid'],
				   'comid'=>$comid,
				   'intername'=>'批量投保',
				   'dtime'=>time(),
			    ];
	            $pay=[    //保单支付信息(假支付)
	               'oderid'=>$insert['orderid'],
	               'name'=>$insert['user'],
	               'zongji'=>$insert['zongji'],
	               'pay'=>$insert['zongji'],
	               'paymethod'=>'微信',
	               'payno'=>GetRandStr(15),
	               'paytime'=>time(),
	            ];
                 if(isset($arr['tijiao'])){ //点击投保
					 if(db('policy')->insert($insert)){
					 	$info='ok';
					 	$interface['status']='1';
		                db('interface')->insert($interface);//插入接口调用表
		                db('pay')->insert($pay);//插入保单支付表
					    echo json_encode($info,JSON_UNESCAPED_UNICODE);
					 }else{
					 	$info='no';
					 	$interface['status']='2';
                        db('interface')->insert($interface);//插入接口调用表
					    echo json_encode($info,JSON_UNESCAPED_UNICODE);
					 }
				}
           }

         }
	 }
	}
	public function revoke_rturns(){//查询已撤销和已退费的保单（可分别查询已撤单和已退费）
		$arr=request()->param();
		if(isset($arr['radio_all'])){ //查询全部
			$info_baodan=Db::query("select * from tp_policy where status1='4' or status1='5'");
			$info['status']='ok';
			$info['list']=$info_baodan;
			return json_encode($info,JSON_UNESCAPED_UNICODE);
		}
        if(isset($arr['radio_che'])){ //查询已撤单
			$info_baodan=Db::query("select * from tp_policy where status1='4' ");
			$info['status']='ok';
			$info['list']=$info_baodan;
			return json_encode($info,JSON_UNESCAPED_UNICODE);
		}
		if(isset($arr['radio_tui'])){ //查询已退费
			$info_baodan=Db::query("select * from tp_policy where status1='5' ");
			$info['status']='ok';
			$info['list']=$info_baodan;
			return json_encode($info,JSON_UNESCAPED_UNICODE);
		}
	}
	public function pay_info(){//支付信息（按经济人、投保人及身份证查询）
        if(isset($_POST['tiaojian'])){
			$info_pay=Db::query("select
			tp_pay.*
			 from tp_pay,tp_policy,tp_broker where tp_pay.oderid=tp_policy.orderid and tp_policy.cid=tp_broker.cid and
             ((tp_broker.name LIKE '%".$_POST['tiaojian']."%') or
              (tp_pay.name LIKE '%".$_POST['tiaojian']."%') or
              (tp_policy.user_idcard LIKE '%".$_POST['tiaojian']."%')) ");
			$info['status']='ok';
			$info['list']=$info_pay;
			return json_encode($info,JSON_UNESCAPED_UNICODE);
		}else{
			$info_pay=Db::query("select * from tp_pay");
			$info['status']='ok';
			$info['list']=$info_pay;
			return json_encode($info,JSON_UNESCAPED_UNICODE);
		}
	}
	public function  tongji(){
	//保单统计（按人、按产品统计保险销售保单数和销售总额，统计数字要实时展现在首页上）
        /*if(isset($_POST['tiaojian'])){
            $num_baodan=Db::query("select count(id) from tp_policy where cid=(select cid from tp_broker where name LIKE '%".$_POST['tiaojian']."%' limit 1) or pid=(select pid from tp_insurance_products where pname LIKE '%".$_POST['tiaojian']."%' limit 1)");
		    $num_jine=Db::query("select SUM(zongji) from tp_policy where cid=(select cid from tp_broker where name LIKE '%".$_POST['tiaojian']."%' limit 1) or pid=(select pid from tp_insurance_products where pname LIKE '%".$_POST['tiaojian']."%' limit 1)");
			$info['num_baodan']=$num_baodan[0]['count(id)'];
			$info['num_jine']=$num_jine[0]['SUM(zongji)'];
			return json_encode($info,JSON_UNESCAPED_UNICODE);
		}else{
		    $num_baodan=Db::query("select count(id) from tp_policy group by cid");
		    $num_jine=Db::query("select SUM(zongji) from tp_policy group by cid");
			$info['num_baodan']=$num_baodan[0]['count(id)'];
			$info['num_jine']=$num_jine[0]['SUM(zongji)'];
			return json_encode($info,JSON_UNESCAPED_UNICODE);
		}*/
		$num['list']=Db::query("select tp_broker.name,count(id) as 'num_baodan',SUM(zongji) as 'num_jine' from tp_policy,tp_broker where tp_policy.cid=tp_broker.cid group by tp_policy.cid");
			return json_encode($num,JSON_UNESCAPED_UNICODE);
	}
	public function del(){//删除经纪人
		$cid=$_POST['session_cid'];
            if($del=db('broker')->where('cid',$cid)->delete()){
            	$info['message']='ok';
                return json_encode($info,JSON_UNESCAPED_UNICODE);
            }else{
            	$info['message']='no';
                return json_encode($info,JSON_UNESCAPED_UNICODE);
            }
	}
}